package utility;

import java.util.ArrayList;
import java.util.List;

import models.Category;
import models.Listvid;
import models.Video;
import news.Rss_item;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DbConnect {

	private static final String DATABASE_NAME="Esport11";
	private static final int DATABASE_VERSION=1;
	//define table news
	public static final String TABLE1_NAME="News";
	public static final String ID="_id";
	public static final String TITLE="title";
	public static final String DES="des";
	public static final String LINK="link";
	public static final String PUBDATE="pubdate";
	
	//define table playlist
	public static final String TABLE2_NAME="Playlist";
	public static final String PID="_id";
	public static final String PRID="id2";
	public static final String PTIT="ptitle";
	public static final String PDATE="date_created";
	public static final String THUMBNAIL="thumbnail";
	public static final String CAT_ID="cid";
	
	//define table video
	public static final String TABLE3_NAME="Videos";
	public static final String VID="_id";
	public static final String VRID="id3";
	public static final String PL_ID="playlist_id";
	public static final String VTI="vtitle";
	public static final String THUMBNAIL_2="thumbnail2";
	public static final String PLAYER="player";
	
	//define table category
	public static final String TABLE4_NAME="Category";
	public static final String CID="_id";
	public static final String CATNAME="name";
	
	private Dbhelp dbh;
	private SQLiteDatabase sqd;
	
	public DbConnect(Context ct){
		dbh=new Dbhelp(ct, DATABASE_NAME, null, DATABASE_VERSION);
	}
	
	public void opend() throws SQLException{
		sqd=dbh.getWritableDatabase();
	}
	
	public void closed(){
		if(sqd!=null){
			sqd.close();
		}
	}
	
	//manipulate news
	public void insert_news(Rss_item ri){
		opend();
		ContentValues cv=new ContentValues();
		cv.put(TITLE, ri.getTitle());
		cv.put(LINK, ri.getLink());
		cv.put(DES, ri.getDes());
		cv.put(PUBDATE, ri.getPubdate());
			
		if(!check_titi(ri.getTitle())){
		sqd.insert(TABLE1_NAME, null, cv);}
		closed();
	}
	
	public boolean check_titi(String ti){
		String q="SELECT title FROM "+TABLE1_NAME+" WHERE title=?";
		opend();
		Cursor cs=sqd.rawQuery(q, new String[]{ti});
		if(cs!=null){
			if(cs.getCount()>0){
				return true;
			}
		}
		return false;
	}
	
	public ArrayList<Rss_item> getAllNews() {
		ArrayList<Rss_item> siteList = new ArrayList<Rss_item>();
		String selectQuery = "SELECT * FROM " + TABLE1_NAME;
		opend();
		Cursor cursor = sqd.rawQuery(selectQuery, null);
		
		if (cursor.moveToFirst()) {
			do {
				Rss_item site = new Rss_item();
				site.setId(Integer.parseInt(cursor.getString(0)));
				site.setTitle(cursor.getString(1));
				site.setDes(cursor.getString(2));
				site.setPubdate(cursor.getString(3));
				site.setLink(cursor.getString(4));
				siteList.add(site);
			} while (cursor.moveToNext());
		}
		cursor.close();
		closed();

		return siteList;
	}
	
	public void delnews(int id){
		opend();
		sqd.delete(TABLE1_NAME,ID+"=?" ,new String[]{String.valueOf(id)});
		closed();
	}
	
	//---------------------------------------------------------------
	//Manipulate playlist
	public void insert_playlist(Listvid li){
		opend();
		ContentValues cv=new ContentValues();
		cv.put(PRID, li.getLid());
		cv.put(PTIT, li.getLiti());
		cv.put(PDATE, li.getPdate());
		cv.put(THUMBNAIL, li.getLithum());
		cv.put(CAT_ID, li.getCat_id());
		
		if(!check_playlist(li.getLid())){
		sqd.insert(TABLE2_NAME, null, cv);}
		closed();
	}
	
	public boolean check_playlist(String ti){
		String q="SELECT id2 FROM "+TABLE2_NAME+" WHERE id2=?";
		opend();
		Cursor cs=sqd.rawQuery(q, new String[]{ti});
		if(cs!=null){
			if(cs.getCount()>0){
				return true;
			}
		}
		return false;
	}
	
	public ArrayList<Listvid> getAllPlaylist(int of,int lim) {
		ArrayList<Listvid> siteList = new ArrayList<Listvid>();
		String selectQuery = "SELECT * FROM " + TABLE2_NAME+ " WHERE _id <= (SELECT MAX(_id) - ? FROM Playlist )"+ " ORDER BY "+ PID +" Limit ?";
		sqd=dbh.getReadableDatabase();
		Cursor cursor = sqd.rawQuery(selectQuery,new String[]{String.valueOf(of),String.valueOf(lim)});
		
		if (cursor.moveToFirst()) {
			do {
				Listvid site = new Listvid();
				site.setLid(cursor.getString(1));
				site.setLiti(cursor.getString(2));
				site.setLithum(cursor.getString(4));
				site.setCat_id(Integer.parseInt(cursor.getString(5)));
				siteList.add(site);
			} while (cursor.moveToNext());
		}
		cursor.close();
		closed();

		return siteList;
	}
	
	public ArrayList<Listvid> getAllPlaylist_2(int cat,int lim) {
		ArrayList<Listvid> siteList = new ArrayList<Listvid>();
		String selectQuery = "SELECT * FROM " + TABLE2_NAME+" WHERE cid=?" + " Limit ?";
		sqd=dbh.getReadableDatabase();
		Cursor cursor = sqd.rawQuery(selectQuery,new String[]{String.valueOf(cat),String.valueOf(lim)});
		
		if (cursor.moveToFirst()) {
			do {
				Listvid site = new Listvid();
				site.setLid(cursor.getString(1));
				site.setLiti(cursor.getString(2));
				site.setLithum(cursor.getString(4));
				site.setCat_id(Integer.parseInt(cursor.getString(5)));
				siteList.add(site);
			} while (cursor.moveToNext());
		}
		cursor.close();
		closed();

		return siteList;
	}
	
	public ArrayList<Listvid> getAllPlaylist_3(int of) {
		ArrayList<Listvid> siteList = new ArrayList<Listvid>();
		String selectQuery = "SELECT * FROM " + TABLE2_NAME+" WHERE cid=?";
		sqd=dbh.getReadableDatabase();
		Cursor cursor = sqd.rawQuery(selectQuery,new String[]{String.valueOf(of)});
		
		if (cursor.moveToFirst()) {
			do {
				Listvid site = new Listvid();
				site.setLid(cursor.getString(1));
				site.setLiti(cursor.getString(2));
				site.setLithum(cursor.getString(4));
				site.setCat_id(Integer.parseInt(cursor.getString(5)));
				siteList.add(site);
			} while (cursor.moveToNext());
		}
		cursor.close();
		closed();

		return siteList;
	}
	
	public ArrayList<Listvid> getAllPlaylist_4(int of) {
		ArrayList<Listvid> siteList = new ArrayList<Listvid>();
		String selectQuery = "SELECT * FROM " + TABLE2_NAME+" limit ?";
		sqd=dbh.getReadableDatabase();
		Cursor cursor = sqd.rawQuery(selectQuery,new String[]{String.valueOf(of)});
		
		if (cursor.moveToFirst()) {
			do {
				Listvid site = new Listvid();
				site.setLid(cursor.getString(1));
				site.setLiti(cursor.getString(2));
				site.setLithum(cursor.getString(4));
				site.setCat_id(Integer.parseInt(cursor.getString(5)));
				siteList.add(site);
			} while (cursor.moveToNext());
		}
		cursor.close();
		closed();

		return siteList;
	}
	
	public ArrayList<Listvid> Search_pl(String of) {
		ArrayList<Listvid> siteList = new ArrayList<Listvid>();
		String selectQuery = "SELECT * FROM " + TABLE2_NAME+" WHERE "+ PTIT +" LIKE '%"+of+"%'";
		sqd=dbh.getReadableDatabase();
		Cursor cursor = sqd.rawQuery(selectQuery,null);
		
		if (cursor.moveToFirst()) {
			do {
				Listvid site = new Listvid();
				site.setLid(cursor.getString(1));
				site.setLiti(cursor.getString(2));
				site.setLithum(cursor.getString(4));
				site.setCat_id(Integer.parseInt(cursor.getString(5)));
				siteList.add(site);
			} while (cursor.moveToNext());
		}
		cursor.close();
		closed();

		return siteList;
	}
	
	public ArrayList<Listvid> Search_pl_2(String of) {
		ArrayList<Listvid> siteList = new ArrayList<Listvid>();
		String selectQuery = "SELECT * FROM " + TABLE2_NAME+" WHERE "+ PTIT +" LIKE '%"+of+"'";
		sqd=dbh.getReadableDatabase();
		Cursor cursor = sqd.rawQuery(selectQuery,null);
		
		if (cursor.moveToFirst()) {
			do {
				Listvid site = new Listvid();
				site.setLid(cursor.getString(1));
				site.setLiti(cursor.getString(2));
				site.setLithum(cursor.getString(4));
				site.setCat_id(Integer.parseInt(cursor.getString(5)));
				siteList.add(site);
			} while (cursor.moveToNext());
		}
		cursor.close();
		closed();

		return siteList;
	}
	
	public void del_playlist(int id){
		opend();
		sqd.delete(TABLE2_NAME,PID+"=?" ,new String[]{String.valueOf(id)});
		closed();
	}
	
	//---------------------------------------------------------------
	//manipulate videos
	public void insert_video(Video vi){
		opend();
		ContentValues cv=new ContentValues();
		cv.put(VRID, vi.getVid());
		cv.put(VTI, vi.getTitle());
		cv.put(THUMBNAIL_2, vi.getThumbUrl());
		cv.put(PLAYER, vi.getUrl());
		cv.put(PL_ID, vi.getPl_id());
		
		if(!check_video(vi.getVid())){
		sqd.insert(TABLE3_NAME, null, cv);}
		closed();
	}
	
	public boolean check_video(String vi){
		String q="SELECT id3 FROM "+TABLE3_NAME+" WHERE id3=?";
		opend();
		Cursor cs=sqd.rawQuery(q, new String[]{vi});
		if(cs!=null){
			if(cs.getCount()>0){
				return true;
			}
		}
		return false;
	}
	
	public ArrayList<Video> getAllVideo() {
		ArrayList<Video> siteList = new ArrayList<Video>();
		String selectQuery = "SELECT * FROM " + TABLE3_NAME;
		opend();
		Cursor cursor = sqd.rawQuery(selectQuery, null);
		
		if (cursor.moveToFirst()) {
			do {
				Video site = new Video();
				site.setId(Integer.parseInt(cursor.getString(0)));
				site.setTitle(cursor.getString(2));
				site.setThumUrl(cursor.getString(3));
				site.setUrl(cursor.getString(4));
				siteList.add(site);
			} while (cursor.moveToNext());
		}
		cursor.close();
		closed();

		return siteList;
	}
	
	public void del_video(int id){
		opend();
		sqd.delete(TABLE3_NAME,VID+"=?" ,new String[]{String.valueOf(id)});
		closed();
	}
	
	//---------------------------------------------------------------
	//Manipulate category
	public void insert_cate(Category ci){
		opend();
		ContentValues cv=new ContentValues();
		cv.put(CATNAME, ci.getName());		
		if(!check_cate(ci.getName())){
		sqd.insert(TABLE4_NAME, null, cv);}
		closed();
	}
	
	public boolean check_cate(String ci){
		String q="SELECT name FROM "+TABLE4_NAME+" WHERE name=?";
		opend();
		Cursor cs=sqd.rawQuery(q, new String[]{ci});
		if(cs!=null){
			if(cs.getCount()>0){
				return true;
			}
		}
		return false;
	}
	public void del_cate(int id){
		opend();
		sqd.delete(TABLE4_NAME,CID+"=?" ,new String[]{String.valueOf(id)});
		closed();
	}
	
	//---------------------------------------------------------------
	//Dbhelper
	private class Dbhelp extends SQLiteOpenHelper{
		public Dbhelp(Context ct,String name,CursorFactory cf,int vs){
			super(ct, name, cf, vs);
		}

		String q="CREATE TABLE " +TABLE1_NAME +
				"("+ID+" INTEGER PRIMARY KEY AUTOINCREMENT," +
				TITLE + " TEXT," + 
				DES + " TEXT," +
				PUBDATE + " TEXT," +
				LINK + " TEXT );";
		
		String q1="CREATE TABLE " +TABLE2_NAME +
				"("+PID+" INTEGER PRIMARY KEY AUTOINCREMENT," +
				PRID + " TEXT NOT NULL," + 
				PTIT + " TEXT," +
				PDATE + " TEXT,"+
				THUMBNAIL+ " TEXT,"+
				CAT_ID+" INTEGER);";
		
		String q2="CREATE TABLE " +TABLE3_NAME +
				"("+VID+" INTEGER PRIMARY KEY AUTOINCREMENT," +
				VRID + " TEXT NOT NULL," + 
				VTI + " TEXT," +
				THUMBNAIL_2 + " TEXT," +
				PLAYER+" TEXT," +
				PL_ID +" INTEGER);";
		
		String q3="CREATE TABLE " +TABLE4_NAME +
				"("+CID+" INTEGER PRIMARY KEY AUTOINCREMENT," +
				CATNAME + " TEXT);"; 
		
		@Override
		public void onCreate(SQLiteDatabase db) {
			// TODO Auto-generated method stub
			db.execSQL(q);
			db.execSQL(q1);
			db.execSQL(q2);
			db.execSQL(q3);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldv, int newv) {
			// TODO Auto-generated method stub
			Log.w("Upgrade DATABASE","Upgrade db from "+ oldv + "to "+ newv +" version");
			db.execSQL("DROP TABLE IF EXISTS "+ TABLE1_NAME);
			db.execSQL("DROP TABLE IF EXISTS "+ TABLE2_NAME);
			db.execSQL("DROP TABLE IF EXISTS "+ TABLE3_NAME);
			db.execSQL("DROP TABLE IF EXISTS "+ TABLE4_NAME);
			onCreate(db);
		}
		
	}
}
